odbc load, exec("select * from dbo.riksarkivet") dsn("[Project]") clear
foreach var of varlist RPG1 RPG2 RPG3 RPG4 STP1 STP2 STP3 STP4 {
replace `var'="." if `var'=="NULL"
}
drop  APROV RPGT
replace BEFL="." if BEFL=="├ì"
replace PSYF ="-1" if PSYF=="A"
replace PSYF ="-2" if PSYF=="B"
replace PSYF ="-3" if PSYF=="C"

replace PGRP ="." if PGRP=="┬░" 
replace MUSK ="." if MUSK=="┬®" | MUSK=="-" | MUSK=="!" 
replace FYSA ="." if FYSA=="┬¢" | FYSA=="┬¿" | FYSA=="┬Â" | FYSA=="z"  | FYSA=="&"
replace STPT ="." if STPT=="D" | STPT=="K" 
replace BARI ="." if BARI=="P3" 

destring, replace

sort LopNr_PersonNr
foreach var of varlist BEFL PSYF PGRP MUSK FYSA RPG1 RPG2 RPG3 RPG4 STP1 STP2 STP3 STP4 STPT BARI {
by LopNr_PersonNr: egen `var'2=max(`var') 
replace `var'=`var'2 if `var'!=`var'2 & !missing(`var'2)
drop `var'2
}

duplicates drop LopNr_PersonNr, force

save "[Folder]\RARM.dta", replace


odbc load, exec("select * from dbo.rekrmynd2") dsn("[Project]") clear
destring, replace
sort LopNr_PersonNr
by LopNr_PersonNr: egen gkap2=max(gkap)
by LopNr_PersonNr: egen gvkap2=max(gvkap)
by LopNr_PersonNr: egen gckap2=max(gckap)
replace gkap=gkap2 if gkap!=gkap2 & !missing(gkap2)
replace gvkap=gkap2 if gvkap!=gvkap2 & !missing(gvkap2)
replace gckap=gkap2 if gckap!=gckap2 & !missing(gckap2)
drop gkap2 gvkap2 gckap2

duplicates drop LopNr_PersonNr, force

merge 1:1 LopNr_PersonNr using  "[Folder]\RARM.dta"

rename _merge RARM_merge
save "[Folder]\RARM.dta", replace
